﻿#region "Imports"
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using Practico2.DataTypes;

#endregion

namespace Practico2.DataAccess
{

    public static class NoticiaDataAccess
    {

        public static int InsertNoticia(DTNoticia toInsert)
        {
            int toReturn = 0;
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            SqlDataReader reader = null;
            string insertCommandText = "INSERT INTO Noticia([IdCiudad],[FechaNoticia],[TituloNoticia],[DescripcionNoticia]) VALUES (@IdCiudad,@FechaNoticia,@TituloNoticia,@DescripcionNoticia) ; SELECT @@Identity";
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }

            SqlCommand command;


            if (useTransaction)
            {
                command = new SqlCommand(insertCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(insertCommandText, connection);
            }

            if (toInsert.IsIdCiudadNull)
            {
                command.Parameters.Add(new SqlParameter("@IdCiudad", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@IdCiudad", toInsert.IdCiudad));
            }
            if (toInsert.IsFechaNoticiaNull)
            {
                command.Parameters.Add(new SqlParameter("@FechaNoticia", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@FechaNoticia", toInsert.FechaNoticia));
            }
            if (toInsert.IsTituloNoticiaNull)
            {
                command.Parameters.Add(new SqlParameter("@TituloNoticia", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@TituloNoticia", toInsert.TituloNoticia));
            }
            if (toInsert.IsDescripcionNoticiaNull)
            {
                command.Parameters.Add(new SqlParameter("@DescripcionNoticia", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@DescripcionNoticia", toInsert.DescripcionNoticia));
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }

                reader = command.ExecuteReader();
                reader.Read();
                toReturn = Convert.ToInt32(reader.GetValue(0));
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }

                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static void UpdateNoticia(DTNoticia toUpdate)
        {
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string updateCommandText = "UPDATE Noticia SET [IdCiudad]=@IdCiudad,[FechaNoticia]=@FechaNoticia,[TituloNoticia]=@TituloNoticia,[DescripcionNoticia]=@DescripcionNoticia WHERE [IdNoticia]=@IdNoticia";

            SqlCommand command;
            if (useTransaction)
            {
                command = new SqlCommand(updateCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(updateCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdNoticia", toUpdate.IdNoticia));

            if (toUpdate.IsIdCiudadNull)
            {
                command.Parameters.Add(new SqlParameter("@IdCiudad", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@IdCiudad", toUpdate.IdCiudad));
            }
            if (toUpdate.IsFechaNoticiaNull)
            {
                command.Parameters.Add(new SqlParameter("@FechaNoticia", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@FechaNoticia", toUpdate.FechaNoticia));
            }
            if (toUpdate.IsTituloNoticiaNull)
            {
                command.Parameters.Add(new SqlParameter("@TituloNoticia", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@TituloNoticia", toUpdate.TituloNoticia));
            }
            if (toUpdate.IsDescripcionNoticiaNull)
            {
                command.Parameters.Add(new SqlParameter("@DescripcionNoticia", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@DescripcionNoticia", toUpdate.DescripcionNoticia));
            }


            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();

            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }

        }

        public static void DeleteNoticia(Int32 idNoticia)
        {

            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            string deleteCommandText = "DELETE FROM Noticia WHERE [IdNoticia]=@IdNoticia";

            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }

            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(deleteCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(deleteCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdNoticia", idNoticia));



            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }

        }

        public static DTNoticia GetNoticia(Int32 idNoticia)
        {

            string selectCommandText = String.Format("SELECT {0} FROM [Noticia] WHERE [IdNoticia]=@IdNoticia", GetNoticiaFields());
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdNoticia", idNoticia));


            DTNoticia current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetNoticiaInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;
        }

        public static List<DTNoticia> ListNoticia()
        {

            List<DTNoticia> toReturn = new List<DTNoticia>();
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string selectCommandText = String.Format("SELECT {0} FROM [Noticia]", GetNoticiaFields());
            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DTNoticia current = GetNoticiaInfo(reader, 0);
                    toReturn.Add(current);
                }
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static string GetNoticiaFields()
        {
            return " [Noticia].[IdNoticia],[Noticia].[IdCiudad],[Noticia].[FechaNoticia],[Noticia].[TituloNoticia],[Noticia].[DescripcionNoticia] ";
        }

        public static DTNoticia GetNoticiaInfo(SqlDataReader reader, int index)
        {
            DTNoticia current = new DTNoticia();
            object temp;

            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.IdNoticia = (Int32)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.IdCiudad = (Int32)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.FechaNoticia = (DateTime)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.TituloNoticia = (String)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.DescripcionNoticia = (String)temp;
            }
            index++;


            return current;
        }


        public static List<DTNoticia> ObtenerNoticiasPorCiudad(int idCiudad)
        {

            List<DTNoticia> toReturn = new List<DTNoticia>();
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }
            string selectCommandText = String.Format("SELECT {0} FROM [Noticia] where [Noticia].[IdCiudad] = @idCiudad", GetNoticiaFields());
            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }
            command.Parameters.Add(new SqlParameter("@idCiudad", idCiudad));
            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DTNoticia current = GetNoticiaInfo(reader, 0);
                    toReturn.Add(current);
                }
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return toReturn;            
        }
    }
}